import pymysql


class ServerDB:
    def __init__(self, kwarg):
        self.db = pymysql.connect(**kwarg)
        self.cur = self.db.cursor()

    def inquire_word(self, data):
        sql = "select mean from words where word=%s;"
        results = self.inquire(sql, data)
        return results

    def inquire_logs(self, data):
        sql = "select id from dict.user where user=%s;"
        user_id = self.inquire(sql, data)
        sql = """select u2.user,w.word,w.mean,log_time from logs 
                                    left join user u2 on u2.id = logs.user_id 
                                    left join words w on w.wdi = logs.words_id
                                    where user_id=%s;"""
        results = self.inquire(sql, user_id)
        return results

    def inquire_user(self, data):
        sql = """select password from user where user=%s;"""
        results = self.inquire(sql, data)
        return results

    def inquire(self, sql, data):
        try:
            self.cur.execute(sql, data)
        except Exception as e:
            self.db.rollback()
            print(e)
        else:
            self.db.commit()
        return self.cur.fetchmany(10)

    def revise(self, sql, datas):
        try:
            self.cur.execute(sql, datas)
        except Exception as e:
            self.db.rollback()
            print(e)
            return False
        else:
            self.db.commit()
            return True

    def write_logs(self, user, words):
        sql = "select wdi from words where word=%s;"
        words_id = self.inquire(sql,words)
        sql = "select id from dict.user where user=%s;"
        user_id = self.inquire(sql, user)
        sql = "insert into logs(user_id,words_id) value(%s,%s);"
        results = self.revise(sql, [user_id[0][0], words_id[0][0]])
        return results

    def write_user(self, user, password):
        sql = "insert into user(user, password) value (%s,%s);"
        results = self.revise(sql, [user, password])
        return results

    def delete_user(self, user):
        sql = "delete from user where user=%s;"
        results = self.revise(sql, user)
        return results
    def close(self):
        self.cur.close()
        self.db.close()


